package com.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.bean.Field;
import com.db.DataBase;

public class FieldDao extends DataBase {

	private String tableName = "T_TOOL";
	private boolean flag = false;
	
	public boolean add(Field obj){
		
		flag = true;
		StringBuilder sql  = new StringBuilder("INSERT ");
		sql.append( tableName);
		sql.append("(CREATE_TIME,MODIFY_TIME,P_ID,T_NAME,T_MODIFY,T_SEQUENCE) ");
		sql.append("VALUES(NOW(),NOW(),?,?,?,?)");
		
		try {
			preparedStatement = conn.prepareStatement(sql.toString());
			int index = 1;
			preparedStatement.setLong(index++, obj.getpId());
			preparedStatement.setString(index++, obj.getName().toUpperCase().trim());
			preparedStatement.setString(index++, obj.getModify().trim());
			preparedStatement.setInt(index++, obj.getSeq());
			preparedStatement.execute();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag = false;
		}finally{
			close();
		}
		
		return flag;
	}
	
	public boolean del(long id){
		
		flag = true;
		
		StringBuilder sql  = new StringBuilder("UPDATE ");
		sql.append( tableName );
		sql.append(" SET MODIFY_TIME = NOW(),DEL_STATUS = 1");
		sql.append(" WHERE ID = ");
		sql.append(id);
		
		try {
			preparedStatement = conn.prepareStatement(sql.toString());
			preparedStatement.execute();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag = false;
		}finally{
			close();
		}
		
		return flag;
	}
	
	public boolean update(Field obj){
		
		flag = true;
		StringBuilder sql = new StringBuilder("UPDATE ");
		sql.append(tableName);
		sql.append(" SET MODIFY_TIME = NOW(),");
		sql.append(" T_NAME = ?,");
		sql.append(" T_MODIFY = ?, ");
		sql.append(" T_SEQUENCE = ? WHERE ID = ?");
		
		try {
			preparedStatement = conn.prepareStatement(sql.toString());
			int index = 1;
			preparedStatement.setString(index++, obj.getName().toUpperCase().trim());
			preparedStatement.setString(index++, obj.getModify().trim());
			preparedStatement.setInt(index++, obj.getSeq());
			preparedStatement.setLong(index++, obj.getId());
			preparedStatement.execute();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag = false;
		}finally{
			close();
		}
		
		return flag ;
	}
	
	public Field get(Long id){
		
		StringBuilder sql = new StringBuilder("SELECT ");
		sql.append("t.ID,");
		sql.append("t.CREATE_TIME,");
		sql.append("t.MODIFY_TIME,");
		sql.append("t.T_NAME,");
		sql.append("t.T_MODIFY,");
		sql.append("t.T_SEQUENCE,");
		sql.append("p.T_NAME PNAME ");
		sql.append("FROM ");
		sql.append(tableName);
		sql.append(" t LEFT JOIN T_TOOL p ON t.P_ID = p.ID WHERE t.DEL_STATUS = 0 AND t.P_ID > 0 AND t.ID = ? ");
		
		try {
			preparedStatement = conn.prepareStatement(sql.toString());
			int index = 1;
			preparedStatement.setLong(index++, id);
			resultSet = preparedStatement.executeQuery();
			if(resultSet.next()){
				Field obj = new Field();
				obj.setId(resultSet.getLong("ID"));
				obj.setCreateTime(resultSet.getTimestamp("CREATE_TIME"));
				obj.setModifyTime(resultSet.getTimestamp("MODIFY_TIME"));
				obj.setName(resultSet.getString("T_NAME"));
				obj.setModify(resultSet.getString("T_MODIFY"));
				obj.setSeq(resultSet.getInt("T_SEQUENCE"));
				obj.setpName(resultSet.getString("PNAME"));
				return obj;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		return null;
	}
	
	public List<Field> query(Long tableId, String field){
		
		StringBuilder sql = new StringBuilder("SELECT ");
		sql.append("t.ID,");
		sql.append("t.CREATE_TIME,");
		sql.append("t.MODIFY_TIME,");
		sql.append("t.T_NAME,");
		sql.append("t.T_MODIFY,");
		sql.append("t.T_SEQUENCE,");
		sql.append("p.T_NAME PNAME ");
		sql.append("FROM ");
		sql.append(tableName);
		sql.append(" t LEFT JOIN T_TOOL p ON t.P_ID = p.ID WHERE 1=1 ");
		if(null != field && !"".equals(field)){
			sql.append("AND  t.T_NAME LIKE ? ");
		}
		sql.append("AND t.P_ID = ? ");
		sql.append("AND t.DEL_STATUS = 0 ");
		sql.append("ORDER BY t.T_SEQUENCE ASC,t.T_NAME ASC ");
		
		List<Field> objs = new ArrayList<Field>();
		
		try {
			
			preparedStatement = conn.prepareStatement(sql.toString());
			int index = 1;
			if(null != field && !"".equals(field)){
				preparedStatement.setString(index++, "%" + field.trim().toUpperCase() + "%");
			}
			preparedStatement.setLong( index++, tableId);
			resultSet = preparedStatement.executeQuery();
			
			Field obj;
			while(resultSet.next()){
				obj = new Field();
				obj.setId(resultSet.getLong("ID"));
				obj.setCreateTime(resultSet.getTimestamp("CREATE_TIME"));
				obj.setModifyTime(resultSet.getTimestamp("MODIFY_TIME"));
				obj.setName(resultSet.getString("T_NAME"));
				obj.setModify(resultSet.getString("T_MODIFY"));
				obj.setSeq(resultSet.getInt("T_SEQUENCE"));
				obj.setpName(resultSet.getString("PNAME"));
				objs.add(obj);
			}
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		
		return objs;
	}
	
}
